"""
Case Type   : GSC功能模块
Case Name   : GSC相关系统函数用户访问权限验证
Create At   : 2023/5/23
Owner       : opentestcase024
Description :
    step1:创建系统管理员用户和普通用户;
    step2:普通用户查询GSC相关函数;
    step2.1:备机查询gs_gsc_memory_detail;
    step2.2:备机查询gs_lsc_memory_detail;
    step2.3:备机查询gs_gsc_dbstat_info;
    step2.4:备机查询gs_gsc_table_detail;
    step2.5:备机查询gs_gsc_catalog_detail;
    step2.6:备机查询gs_gsc_clean;
    step3:系统用户查询GSC相关函数;
    step3.1:备机查询gs_gsc_memory_detail;
    step3.2:备机查询gs_lsc_memory_detail;
    step3.3:备机查询gs_gsc_dbstat_info;
    step3.4:备机查询gs_gsc_table_detail;
    step3.5:备机查询gs_gsc_catalog_detail;
    step3.6:备机查询gs_gsc_clean;
Expect      :
    step1:创建系统管理员用户和普通用户; expect:创建成功
    step2:普通用户查询GSC相关函数;
    step2.1:备机查询gs_gsc_memory_detail; expect:查询正常
    step2.2:备机查询gs_lsc_memory_detail; expect:查询正常
    step2.3:备机查询gs_gsc_dbstat_info; expect:无权限
    step2.4:备机查询gs_gsc_table_detail; expect:无权限
    step2.5:备机查询gs_gsc_catalog_detail; expect:无权限
    step2.6:备机查询gs_gsc_clean; expect:无权限
    step3:系统用户查询GSC相关函数;
    step3.1:备机查询gs_gsc_memory_detail; expect:查询正常
    step3.2:备机查询gs_lsc_memory_detail; expect:查询正常
    step3.3:备机查询gs_gsc_dbstat_info; expect:查询正常
    step3.4:备机查询gs_gsc_table_detail; expect:查询正常
    step3.5:备机查询gs_gsc_catalog_detail; expect:查询正常
    step3.6:备机查询gs_gsc_clean; expect:查询正常
History     :
    modified by peilinqian at 2024-6-17: 用例适配资源池化
"""

import os
import re
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import macro

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.log.info("----this is setup----")
        self.sta_sh = CommonSH('Standby1DbUser')
        self.constant = Constant()
        self.pwd = macro.PASSWD_INITIAL
        self.common_user = 'u_gsc0049_1'
        self.sys_user = 'u_gsc0049_2'
        self.con_com = f'-U {self.common_user} -W {self.pwd}'
        self.con_sys = f'-U {self.sys_user} -W {self.pwd}'
        self.err_msg = 'ERROR:  Fail to .*.\n' \
                       'DETAIL:  Insufficient privilege to .*.'

    def test_main(self):
        step_txt = '----step1:创建系统管理员用户和普通用户; expect:创建成功----'
        self.log.info(step_txt)
        create_sql = f"drop user if exists {self.common_user} cascade; " \
            f"drop user if exists {self.sys_user} cascade; " \
            f"create user {self.common_user} password '{self.pwd}';" \
            f"create user {self.sys_user} sysadmin password '{self.pwd}';"
        create_result = pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        assert_flag = create_result.splitlines().count(
            self.constant.CREATE_ROLE_SUCCESS_MSG)
        self.assertEqual(assert_flag, 2, "执行失败" + step_txt)

        step_txt = '----step2:普通用户查询GSC相关函数;----'
        self.log.info(step_txt)
        step_txt = '----step2.1:备机查询gs_gsc_memory_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_memory_detail',
                                          self.con_com)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step2.2:备机查询gs_lsc_memory_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_lsc_memory_detail',
                                          self.con_com)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step2.3:备机查询gs_gsc_dbstat_info; expect:无权限----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_dbstat_info()',
                                          self.con_com)
        self.assertGreater(len(re.findall(self.err_msg, select_result)), 0,
                           '执行失败:' + step_txt)

        step_txt = '----step2.4:备机查询gs_gsc_table_detail; expect:无权限----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_table_detail()',
                                          self.con_com)
        self.assertGreater(len(re.findall(self.err_msg, select_result)), 0,
                           '执行失败:' + step_txt)

        step_txt = '----step2.5:备机查询gs_gsc_catalog_detail; expect:无权限----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_catalog_detail()',
                                          self.con_com)
        self.assertGreater(len(re.findall(self.err_msg, select_result)), 0,
                           '执行失败:' + step_txt)

        step_txt = '----step2.6:备机查询gs_gsc_clean; expect:无权限----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_clean()', self.con_com)
        self.assertGreater(len(re.findall(self.err_msg, select_result)), 0,
                           '执行失败:' + step_txt)

        step_txt = '----step3:系统用户查询GSC相关函数;----'
        self.log.info(step_txt)
        step_txt = '----step3.1:备机查询gs_gsc_memory_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_memory_detail',
                                          self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step3.2:备机查询gs_lsc_memory_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_lsc_memory_detail',
                                          self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step3.3:备机查询gs_gsc_dbstat_info; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_dbstat_info()',
                                          self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step3.4:备机查询gs_gsc_table_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_table_detail()',
                                          self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step3.5:备机查询gs_gsc_catalog_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_catalog_detail()',
                                          self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step3.6:备机查询gs_gsc_clean; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_clean()', self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

    def check_select(self, object_name, con_user):
        """
        :param object_name: 查询对象名称
        :param con_user: 查询连接信息
        :return: 查询结果
        """
        select_sql1 = f'select count(*) from {object_name};'
        select_result = self.sta_sh.execut_db_sql(select_sql1,
                                                  sql_type=con_user)
        self.log.info(select_result)
        return select_result

    def tearDown(self):
        self.log.info('----this is teardown----')
        step_txt = '----删除用户; expect:成功----'
        self.log.info(step_txt)
        clean_sql = f"drop user if exists {self.common_user} cascade; " \
            f"drop user if exists {self.sys_user} cascade; "
        clean_result = pri_sh.execut_db_sql(clean_sql)
        self.log.info(clean_result)

        drop_count = clean_result.count(self.constant.DROP_ROLE_SUCCESS_MSG)
        self.assertEqual(drop_count, 2, "执行失败" + step_txt)

        self.log.info(f'-----{os.path.basename(__file__)} end-----')
